package com.example.demotest.repository;

import com.example.demotest.pojo.Product;
import com.example.demotest.pojo.ProductCheckStatus;
import com.example.demotest.pojo.ProductSoldStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;



@Repository
public interface ProductRepository extends JpaRepository<Product, Integer>, JpaSpecificationExecutor<Product> {
    // 基础查询方法
    Optional<Product> findById(Integer id);

    // 分类查询
    Page<Product> findByProductCategory_Id(Integer categoryId, Pageable pageable);

    // 状态查询
    Page<Product> findByStatusCheck(ProductCheckStatus statusCheck, Pageable pageable);
    Page<Product> findByStatusCheckAndStatusSold(ProductCheckStatus productCheckStatus, ProductSoldStatus productSoldStatus, Pageable pageable);
    Page<Product> findByStatusSold(ProductSoldStatus productSoldStatus, Pageable pageable);

    // 用户相关查询
    List<Product> findByUserIdAndStatusSoldAndStatusCheck(
            Integer userId,
            ProductSoldStatus statusSold,
            ProductCheckStatus statusCheck
    );

    // 模糊搜索
    Page<Product> findByProductNameContainingOrProductDescriptionContainingAndStatusCheckEquals(
            String productName, String productDescription, String statusCheck, Pageable pageable);

    // 热门商品查询
    @Query(value = """
        SELECT p.* FROM table_product p
        WHERE p.status_check = 'APPROVED' 
        AND p.status_sold = 'ON_SALE'
        ORDER BY p.view_count DESC
        LIMIT :limit OFFSET :offset
        """, nativeQuery = true)
    List<Product> findPopularProducts(@Param("limit") int limit, @Param("offset") int offset);

    // 商品总数统计
    @Query(value = """
        SELECT COUNT(*) FROM table_product 
        WHERE status_check = 'APPROVED' 
        AND status_sold = 'ON_SALE'
        """, nativeQuery = true)
    long countAllProducts();
}
